<?php
namespace Common\Libiary;

class FileExcel{

    public function __construct() {

        /*导入phpExcel核心类 */
        vendor("Classes.PHPExcel");
        vendor("Classes.PHPExcel.IOFactory");
        vendor("Classes.PHPExcel.Writer.Excel5");
        vendor("Classes.PHPExcel.Writer.Excel2007");
    }


//导入excel内容转换成数组
    public function import($filePath){
        $this->__construct();
        $PHPExcel = new \PHPExcel();

        /**默认用excel2007读取excel，若格式不对，则用之前的版本进行读取*/
        $PHPReader = new\ PHPExcel_Reader_Excel2007();
        if(!$PHPReader->canRead($filePath)){
            $PHPReader = new \PHPExcel_Reader_Excel5();
            if(!$PHPReader->canRead($filePath)){
                echo 'no Excel';
                return;
            }
        }
        $objPHPExcel = $PHPReader->load($filePath);
        $objWorksheet = $objPHPExcel->getActiveSheet();
        $highestRow = $objWorksheet->getHighestRow();
        $highestColumn = $objWorksheet->getHighestColumn();
        $highestColumnIndex = \PHPExcel_Cell::columnIndexFromString($highestColumn);
        $excelData = array();
        for ($row = 1; $row <= $highestRow; $row++) {
            for ($col = 0; $col < $highestColumnIndex; $col++) {
                $excelData[$row][] =(string)$objWorksheet->getCellByColumnAndRow($col, $row)->getValue();
            }
        }
        return $excelData;
    }

//导出Excel表格
    public function export($data,$excelFileName,$sheetTitle){

        /* 实例化类 */
        $objPHPExcel = new \PHPExcel();

        /* 设置输出的excel文件为2007兼容格式 */
        //$objWriter=new PHPExcel_Writer_Excel5($objPHPExcel);//非2007格式
        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);

        /* 设置当前的sheet */
        $objPHPExcel->setActiveSheetIndex(0);

        $objActSheet = $objPHPExcel->getActiveSheet();

        /*设置宽度*/
        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(10);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(60);



        /* sheet标题 */
        $objActSheet->setTitle($sheetTitle);
        $objPHPExcel->getActiveSheet()->setTitle($sheetTitle);

        //定义一个列名数组
        $arr = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z',
            'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV',
            'AW', 'AX', 'AY', 'AZ');

        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $sheetTitle)
            ->setCellValue('A2', '序号')
            ->setCellValue('B2', '标签名')
            ->setCellValue('C2', '粉丝数');
    //$rs为数据库查询出来的结果
    //开始输出excel内容，从第二行开始，把整个excel输出
        for ($i = 0; $i < count($data); $i++) {

            $objPHPExcel->getActiveSheet(0)->setCellValue('A' . ($i + 3), $data[$i]['id']);
            $objPHPExcel->getActiveSheet(0)->setCellValue('B' . ($i + 3), $data[$i]['name']);
            $objPHPExcel->getActiveSheet(0)->setCellValue('C' . ($i + 3), $data[$i]['total']);
        }


        /* 生成到浏览器，提供下载 */
        ob_end_clean();  //清空缓存
        header("Pragma: public");
        header("Expires: 0");
        header("Cache-Control:must-revalidate,post-check=0,pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename="'.$excelFileName.'.xlsx"');
        header("Content-Transfer-Encoding:binary");
        $objWriter->save('php://output');
    }
}